
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/24/2013 10:05:11
-- Generated from EDMX file: C:\Users\liujiny\My Personal Documents\project\workspace.net\Test\Lab\YIBINApprovalProcess\YIBINApprovalProcess.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [YBProcess];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_FormField]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Field] DROP CONSTRAINT [FK_FormField];
GO
IF OBJECT_ID(N'[dbo].[FK_OptionListOptionListValue]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OptionListValue] DROP CONSTRAINT [FK_OptionListOptionListValue];
GO
IF OBJECT_ID(N'[dbo].[FK_RecordRecordValue]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[InstanceValue] DROP CONSTRAINT [FK_RecordRecordValue];
GO
IF OBJECT_ID(N'[dbo].[FK_RecordRecordApproval]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[InstanceApproval] DROP CONSTRAINT [FK_RecordRecordApproval];
GO
IF OBJECT_ID(N'[dbo].[FK_FieldRecordValue]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[InstanceValue] DROP CONSTRAINT [FK_FieldRecordValue];
GO
IF OBJECT_ID(N'[dbo].[FK_ProcessNode]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Node] DROP CONSTRAINT [FK_ProcessNode];
GO
IF OBJECT_ID(N'[dbo].[FK_NodeApproval]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Approval] DROP CONSTRAINT [FK_NodeApproval];
GO
IF OBJECT_ID(N'[dbo].[FK_FormRecord]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Instance] DROP CONSTRAINT [FK_FormRecord];
GO
IF OBJECT_ID(N'[dbo].[FK_NodeRecordApproval]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[InstanceApproval] DROP CONSTRAINT [FK_NodeRecordApproval];
GO
IF OBJECT_ID(N'[dbo].[FK_NodeRecord]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Instance] DROP CONSTRAINT [FK_NodeRecord];
GO
IF OBJECT_ID(N'[dbo].[FK_RoleUserProfile]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserProfile] DROP CONSTRAINT [FK_RoleUserProfile];
GO
IF OBJECT_ID(N'[dbo].[FK_FormForm_Process]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Form_ProcessSet] DROP CONSTRAINT [FK_FormForm_Process];
GO
IF OBJECT_ID(N'[dbo].[FK_ProcessForm_Process]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Form_ProcessSet] DROP CONSTRAINT [FK_ProcessForm_Process];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Form]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Form];
GO
IF OBJECT_ID(N'[dbo].[Field]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Field];
GO
IF OBJECT_ID(N'[dbo].[InstanceApproval]', 'U') IS NOT NULL
    DROP TABLE [dbo].[InstanceApproval];
GO
IF OBJECT_ID(N'[dbo].[InstanceValue]', 'U') IS NOT NULL
    DROP TABLE [dbo].[InstanceValue];
GO
IF OBJECT_ID(N'[dbo].[OptionListValue]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OptionListValue];
GO
IF OBJECT_ID(N'[dbo].[OptionList]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OptionList];
GO
IF OBJECT_ID(N'[dbo].[Instance]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Instance];
GO
IF OBJECT_ID(N'[dbo].[Process]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Process];
GO
IF OBJECT_ID(N'[dbo].[Node]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Node];
GO
IF OBJECT_ID(N'[dbo].[Approval]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Approval];
GO
IF OBJECT_ID(N'[dbo].[UserProfile]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserProfile];
GO
IF OBJECT_ID(N'[dbo].[Role]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Role];
GO
IF OBJECT_ID(N'[dbo].[Form_ProcessSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Form_ProcessSet];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Form'
CREATE TABLE [dbo].[Form] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(200)  NOT NULL,
    [CreateDt] datetime  NOT NULL,
    [LastUpdatedDt] datetime  NOT NULL,
    [LastUpdatedBy] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Field'
CREATE TABLE [dbo].[Field] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(200)  NOT NULL,
    [FormId] int  NOT NULL,
    [Type] nvarchar(100)  NOT NULL,
    [IsRequired] bit  NOT NULL,
    [OptionListId] int  NOT NULL,
    [DisplayOrder] int  NOT NULL,
    [IsWholeRow] bit  NOT NULL
);
GO

-- Creating table 'InstanceApproval'
CREATE TABLE [dbo].[InstanceApproval] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [RecordId] uniqueidentifier  NOT NULL,
    [IsApproved] bit  NOT NULL,
    [IsDenied] bit  NOT NULL,
    [Comment] nvarchar(800)  NOT NULL,
    [NodeId] int  NOT NULL,
    [DefaultApprover] nvarchar(100)  NOT NULL,
    [ApproveBy] nvarchar(100)  NOT NULL,
    [ApproveDate] datetime  NOT NULL,
    [ApproveOrder] int  NOT NULL,
    [IsRole] bit  NOT NULL
);
GO

-- Creating table 'InstanceValue'
CREATE TABLE [dbo].[InstanceValue] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [RecordId] uniqueidentifier  NOT NULL,
    [FieldId] int  NOT NULL,
    [Value] nvarchar(800)  NOT NULL,
    [Text] nvarchar(800)  NOT NULL
);
GO

-- Creating table 'OptionListItem'
CREATE TABLE [dbo].[OptionListItem] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Text] nvarchar(100)  NOT NULL,
    [Value] nvarchar(100)  NOT NULL,
    [IsDefault] bit  NOT NULL,
    [OptionListId] int  NOT NULL,
    [DisplayOrder] int  NOT NULL
);
GO

-- Creating table 'OptionList'
CREATE TABLE [dbo].[OptionList] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Instance'
CREATE TABLE [dbo].[Instance] (
    [RecordId] uniqueidentifier  NOT NULL,
    [Submitter] nvarchar(200)  NOT NULL,
    [SubmitDate] datetime  NOT NULL,
    [IsClosed] bit  NOT NULL,
    [ClosedBy] nvarchar(200)  NOT NULL,
    [CloseDate] datetime  NOT NULL,
    [FormId] int  NOT NULL,
    [CurrentNodeId] int  NOT NULL,
    [IsSubmitted] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Process'
CREATE TABLE [dbo].[Process] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(200)  NOT NULL,
    [CreateDt] datetime  NOT NULL,
    [LastUpdatedDt] datetime  NOT NULL,
    [LastUpdatedBy] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Node'
CREATE TABLE [dbo].[Node] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(200)  NOT NULL,
    [ProcessId] int  NOT NULL,
    [Type] nvarchar(50)  NOT NULL,
    [Order] int  NOT NULL,
    [Condition] int  NOT NULL
);
GO

-- Creating table 'Approval'
CREATE TABLE [dbo].[Approval] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Approver] nvarchar(200)  NOT NULL,
    [NodeId] int  NOT NULL,
    [IsRole] bit  NOT NULL
);
GO

-- Creating table 'UserProfile'
CREATE TABLE [dbo].[UserProfile] (
    [Id] nvarchar(100)  NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Email] nvarchar(100)  NOT NULL,
    [Phone] nvarchar(50)  NOT NULL,
    [CreateDt] datetime  NOT NULL,
    [LastUpdatedDt] datetime  NOT NULL,
    [LastUpdatedBy] nvarchar(100)  NOT NULL,
    [Pwd] nvarchar(200)  NOT NULL,
    [LoginName] nvarchar(200)  NOT NULL,
    [Deligate] nvarchar(200)  NOT NULL,
    [RoleId] int  NOT NULL
);
GO

-- Creating table 'Role'
CREATE TABLE [dbo].[Role] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(200)  NOT NULL,
    [Desc] nvarchar(800)  NOT NULL,
    [CreateDt] datetime  NOT NULL,
    [LastUpdatedDt] datetime  NOT NULL,
    [LastUpdatedBy] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Form_ProcessSet'
CREATE TABLE [dbo].[Form_ProcessSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(200)  NOT NULL,
    [Desc] nvarchar(800)  NOT NULL,
    [IsActive] bit  NOT NULL,
    [FormId] int  NOT NULL,
    [ProcessId] int  NOT NULL,
    [CreateDt] datetime  NOT NULL,
    [LastUpdatedDt] datetime  NOT NULL,
    [LastUpdatedBy] nvarchar(100)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Form'
ALTER TABLE [dbo].[Form]
ADD CONSTRAINT [PK_Form]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Field'
ALTER TABLE [dbo].[Field]
ADD CONSTRAINT [PK_Field]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'InstanceApproval'
ALTER TABLE [dbo].[InstanceApproval]
ADD CONSTRAINT [PK_InstanceApproval]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'InstanceValue'
ALTER TABLE [dbo].[InstanceValue]
ADD CONSTRAINT [PK_InstanceValue]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'OptionListItem'
ALTER TABLE [dbo].[OptionListItem]
ADD CONSTRAINT [PK_OptionListItem]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'OptionList'
ALTER TABLE [dbo].[OptionList]
ADD CONSTRAINT [PK_OptionList]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [RecordId] in table 'Instance'
ALTER TABLE [dbo].[Instance]
ADD CONSTRAINT [PK_Instance]
    PRIMARY KEY CLUSTERED ([RecordId] ASC);
GO

-- Creating primary key on [Id] in table 'Process'
ALTER TABLE [dbo].[Process]
ADD CONSTRAINT [PK_Process]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Node'
ALTER TABLE [dbo].[Node]
ADD CONSTRAINT [PK_Node]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Approval'
ALTER TABLE [dbo].[Approval]
ADD CONSTRAINT [PK_Approval]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserProfile'
ALTER TABLE [dbo].[UserProfile]
ADD CONSTRAINT [PK_UserProfile]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Role'
ALTER TABLE [dbo].[Role]
ADD CONSTRAINT [PK_Role]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Form_ProcessSet'
ALTER TABLE [dbo].[Form_ProcessSet]
ADD CONSTRAINT [PK_Form_ProcessSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [FormId] in table 'Field'
ALTER TABLE [dbo].[Field]
ADD CONSTRAINT [FK_FormField]
    FOREIGN KEY ([FormId])
    REFERENCES [dbo].[Form]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FormField'
CREATE INDEX [IX_FK_FormField]
ON [dbo].[Field]
    ([FormId]);
GO

-- Creating foreign key on [OptionListId] in table 'OptionListItem'
ALTER TABLE [dbo].[OptionListItem]
ADD CONSTRAINT [FK_OptionListOptionListValue]
    FOREIGN KEY ([OptionListId])
    REFERENCES [dbo].[OptionList]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_OptionListOptionListValue'
CREATE INDEX [IX_FK_OptionListOptionListValue]
ON [dbo].[OptionListItem]
    ([OptionListId]);
GO

-- Creating foreign key on [RecordId] in table 'InstanceValue'
ALTER TABLE [dbo].[InstanceValue]
ADD CONSTRAINT [FK_RecordRecordValue]
    FOREIGN KEY ([RecordId])
    REFERENCES [dbo].[Instance]
        ([RecordId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RecordRecordValue'
CREATE INDEX [IX_FK_RecordRecordValue]
ON [dbo].[InstanceValue]
    ([RecordId]);
GO

-- Creating foreign key on [RecordId] in table 'InstanceApproval'
ALTER TABLE [dbo].[InstanceApproval]
ADD CONSTRAINT [FK_RecordRecordApproval]
    FOREIGN KEY ([RecordId])
    REFERENCES [dbo].[Instance]
        ([RecordId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RecordRecordApproval'
CREATE INDEX [IX_FK_RecordRecordApproval]
ON [dbo].[InstanceApproval]
    ([RecordId]);
GO

-- Creating foreign key on [FieldId] in table 'InstanceValue'
ALTER TABLE [dbo].[InstanceValue]
ADD CONSTRAINT [FK_FieldRecordValue]
    FOREIGN KEY ([FieldId])
    REFERENCES [dbo].[Field]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FieldRecordValue'
CREATE INDEX [IX_FK_FieldRecordValue]
ON [dbo].[InstanceValue]
    ([FieldId]);
GO

-- Creating foreign key on [ProcessId] in table 'Node'
ALTER TABLE [dbo].[Node]
ADD CONSTRAINT [FK_ProcessNode]
    FOREIGN KEY ([ProcessId])
    REFERENCES [dbo].[Process]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProcessNode'
CREATE INDEX [IX_FK_ProcessNode]
ON [dbo].[Node]
    ([ProcessId]);
GO

-- Creating foreign key on [NodeId] in table 'Approval'
ALTER TABLE [dbo].[Approval]
ADD CONSTRAINT [FK_NodeApproval]
    FOREIGN KEY ([NodeId])
    REFERENCES [dbo].[Node]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_NodeApproval'
CREATE INDEX [IX_FK_NodeApproval]
ON [dbo].[Approval]
    ([NodeId]);
GO

-- Creating foreign key on [FormId] in table 'Instance'
ALTER TABLE [dbo].[Instance]
ADD CONSTRAINT [FK_FormRecord]
    FOREIGN KEY ([FormId])
    REFERENCES [dbo].[Form]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FormRecord'
CREATE INDEX [IX_FK_FormRecord]
ON [dbo].[Instance]
    ([FormId]);
GO

-- Creating foreign key on [NodeId] in table 'InstanceApproval'
ALTER TABLE [dbo].[InstanceApproval]
ADD CONSTRAINT [FK_NodeRecordApproval]
    FOREIGN KEY ([NodeId])
    REFERENCES [dbo].[Node]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_NodeRecordApproval'
CREATE INDEX [IX_FK_NodeRecordApproval]
ON [dbo].[InstanceApproval]
    ([NodeId]);
GO

-- Creating foreign key on [CurrentNodeId] in table 'Instance'
ALTER TABLE [dbo].[Instance]
ADD CONSTRAINT [FK_NodeRecord]
    FOREIGN KEY ([CurrentNodeId])
    REFERENCES [dbo].[Node]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_NodeRecord'
CREATE INDEX [IX_FK_NodeRecord]
ON [dbo].[Instance]
    ([CurrentNodeId]);
GO

-- Creating foreign key on [RoleId] in table 'UserProfile'
ALTER TABLE [dbo].[UserProfile]
ADD CONSTRAINT [FK_RoleUserProfile]
    FOREIGN KEY ([RoleId])
    REFERENCES [dbo].[Role]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleUserProfile'
CREATE INDEX [IX_FK_RoleUserProfile]
ON [dbo].[UserProfile]
    ([RoleId]);
GO

-- Creating foreign key on [FormId] in table 'Form_ProcessSet'
ALTER TABLE [dbo].[Form_ProcessSet]
ADD CONSTRAINT [FK_FormForm_Process]
    FOREIGN KEY ([FormId])
    REFERENCES [dbo].[Form]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FormForm_Process'
CREATE INDEX [IX_FK_FormForm_Process]
ON [dbo].[Form_ProcessSet]
    ([FormId]);
GO

-- Creating foreign key on [ProcessId] in table 'Form_ProcessSet'
ALTER TABLE [dbo].[Form_ProcessSet]
ADD CONSTRAINT [FK_ProcessForm_Process]
    FOREIGN KEY ([ProcessId])
    REFERENCES [dbo].[Process]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProcessForm_Process'
CREATE INDEX [IX_FK_ProcessForm_Process]
ON [dbo].[Form_ProcessSet]
    ([ProcessId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------